package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Purme;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;
import com.clouds.wms.util.SysParams;

@Repository("purmeDao")
public class PurmeDaoImpl implements PurmeDao {
	private Log log = LogFactory.getLog(getClass());
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	@Override
	public PagesBean getPurmeList(String meaa, String meab, String meac,String startMead, String endMead, int offset, int pageSize)	throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Purme> list = new ArrayList<Purme>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT * FROM purme ");
		if(meaa != null && !"".equals(meaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " meaa = '" + Function.formatSQLString(meaa) + "'");
		}
		if(meab != null && !"".equals(meab)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " meab = '" + Function.formatSQLString(meab) + "'");
		}
		if(meac != null && !"".equals(meac)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " meac = '" + Function.formatSQLString(meac) + "'");
		}else{
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " meac in ('" + SysParams.AUDIT_YARDS_NOT_AUDIT + "','S') ");
		}
		if(startMead != null && !"".equals(startMead)) {
			sql.append( " mead >= '" + Function.formatSQLString(startMead) + "' ");
		}
		if(endMead != null && !"".equals(endMead)) {
			sql.append(" mead <= '" + Function.formatSQLString(endMead) + "' ");
		}
		sql.append(" ORDER BY meab DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Purme>(Purme.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public Purme getPurme(String meaa,String meab) throws DatabaseException {
		String sql = "SELECT * FROM purme WHERE meaa=? and meab = ? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{meaa,meab}, new BeanPropertyRowMapper<Purme>(Purme.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int savePurca(Purme purme) throws DatabaseException {
		String sql = "INSERT INTO purme (meaa,meab,mead) VALUES (:meaa,:meab,:mead)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(purme);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public void delPurmeByAaAb(String meaa, String meab)throws DatabaseException {
		String sql1 = "delete from purme where meaa = ? and meab = ?";
		namedParameterJdbcTemplate.getJdbcOperations().update(sql1, new Object[]{meaa,meab});
	}

}
